import java.sql.*;
import java.util.Scanner;

public class LoginDemo {


    public static final String URL = "jdbc:mysql://localhost:3306/mydatabase?serverTimezone=GMT";

    public static final String USER = "root";

    public static final String PASSWORD = "12345678";

//登陆验证
    public static boolean login(String username, String password) {
        //通过驱动管理获得连接
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            //获得编译对象
            PreparedStatement statement = conn.prepareStatement("select * from t_user where username= ? and password=?");
            //执行查询命令
            statement.setString(1, username);
            statement.setString(2, password);
            //判断结果集是否为空
            ResultSet rs =statement.executeQuery();
            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return false;
    }


    //1.查看所有同学
    public static void TestConnection(){
        try(Connection conn= DriverManager.getConnection(URL,USER, PASSWORD)) {
            System.out.println(conn);
            //获得命令对象
            Statement statement=conn.createStatement();
            System.out.println("连接成功");
            //执行查询命令
            ResultSet rs = statement.executeQuery("select * from t_user");
            //遍历结果集
            while (rs.next()){
                System.out.println("==========================================================================");
                int Id=rs.getInt("id");
                String Name=rs.getString("username");
                String password=rs.getString("password");
                System.out.println(Id+"\t"+Name+"\t"+password+"\t");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //2.添加学生
    public static void TestConnection2(String username,String password){
        try(Connection conn= DriverManager.getConnection(URL,USER, PASSWORD)) {
            System.out.println("连接成功");
            //获得命令对象
            PreparedStatement statement = conn.prepareStatement("insert into t_user(username,password)values(?,?)");
            statement.setString(1, username);
            statement.setString(2, password);
            //执行命令
            int rs = statement.executeUpdate();
            if (rs>0) {
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //3.删除学生
    public static void TestConnection3(int id){
        try(Connection conn= DriverManager.getConnection(URL,USER, PASSWORD)) {
            System.out.println("连接成功");
            //获得命令对象
            PreparedStatement statement = conn.prepareStatement("delete from t_user where id=?");
            statement.setInt(1,id);
            //执行命令
            int rs = statement.executeUpdate();
            if (rs>0) {
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    //4.输入学生信息（不包含id），实现按id更新学生信息功能
    public static void TestConnection4(String username,String password,int id){
        try(Connection conn= DriverManager.getConnection(URL,USER,PASSWORD)) {
            System.out.println("连接成功");
            PreparedStatement statement = conn.prepareStatement("update t_user set username=?,password=? where id=?");
            statement.setString(1,username);
            statement.setString(2,password);
            statement.setInt(3,id);
            //执行命令
            int rs = statement.executeUpdate();
            if (rs>0) {
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }




public static void main(String[] args) {
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入账号：");
            String username = scanner.nextLine();
            System.out.println("请输入密码：");
            String password = scanner.nextLine();
            if(login(username,password)) {
                while (true) {
                    System.out.println("登录成功");
                    System.out.println("请选择您要使用的功能");
                    System.out.println("1.显示所有学生");
                    System.out.println("2.添加学生");
                    System.out.println("3.删除学生");
                    System.out.println("4.修改学生");
                    int option = scanner.nextInt();
                    switch (option) {
                        case 1:
                            TestConnection();
                            break;
                        case 2:
                            Scanner scanner2 = new Scanner(System.in);
                            System.out.println("请输入账号：");
                            String username2 = scanner2.nextLine();
                            System.out.println("请输入密码：");
                            String password2 = scanner2.nextLine();
                            TestConnection2(username2, password2);
                        case 3:
                            Scanner scanner3 = new Scanner(System.in);
                            System.out.println("请输入要删除账号的id：");
                            int id = scanner3.nextInt();
                            TestConnection3(id);
                        case 4:
                            Scanner scanner4 = new Scanner(System.in);
                            System.out.println("请输入要修改的账号id：");
                            int id4 = scanner4.nextInt();
                            System.out.println("请输入修改后的账号：");
                            String username4 = scanner4.next();
                            System.out.println("请输入修改后的密码：");
                            String password4 = scanner4.next();
                            TestConnection4(username4, password4, id4);
                            break;
                        case 5:
                        default:
                            System.out.println("输入错误");
                            break;
                                 }
                        }
                }else{
                    System.out.println("登录失败");
                }


        }

    }
